/**
 * 根据班级口令获取对应的班级
 */
export const GETCLASSIDFROMCOMMANDWORD =
  "select id from class where command_word=?";

/**
 * 学生加入班级
 */
export const ADDCLASS =
  "insert into class_user (class_id, user_id) values(?, ?)";

/**
 * 新建班级
 */
export const CREATECLASS =
  "insert into class (name, command_word, user_id) values(?, ?, ?)";

/**
 * 判断班级是否已经创建过了
 */
export const ISCLASSEXIST = "select id from class where name=?";

/**
 * 根据口令判断班级是否已经创建过了
 */
export const ISCLASSEXISTFROMCOMMANDWORD =
  "select id from class where command_word=?";

/**
 * 通过班级id查找该班级是否存在
 */
export const ISCLASSEXISTFROMID = "select name from class where id=?";

/**
 * 将学生移除班级
 */
export const REMOVECLASS = `delete from class_user where class_id=? and user_id=?`;

/**
 * 判断是否重复加入了班级
 */
export const ISREPEATADDCLASS = `select id from class_user where class_id=? and user_id=?`;

/**
 * 获取所有的班级信息
 */
export const GETCLASS = ` SELECT  id, name, create_time, command_word, (select count(*) from class) total, (select count(*) from class_user where class_id=class.id) count, (select JSON_ARRAYAGG(JSON_OBJECT('id', u.id, 'username', u.username, 'avatar', u.avatar, 'create_time', u.create_time, 'role', u.role)) from class_user cu left join user u on user_id=u.id where class.id=class_id)  students FROM class where user_id=? limit ? offset ? `;

/**
 * 获取一个班级的信息
 */
export const GETONCLASS = `
SELECT  id, name, create_time, command_word FROM class where id=?
`;

/**
 * 删除班级
 */
export const DELCLASS = `delete from class where id=? and user_id=?`;

/**
 * 判断用户是否有该班级
 */
export const ISUSERHASCLASS = "select id from class where user_id=? and id=?";

/**
 * 编辑班级信息
 */
export const EDITCLASS = "update class set name=? where id=?";

/**
 * 获取学生的班级
 */
export const GETSTUCLASS = `select c.id id, c.name name, c.create_time create_time, json_object("name", u.username) user from class_user cs left join class c on cs.class_id=c.id left join user u on c.user_id=u.id where cs.user_id=?`

/**
 * 学生退出班级
 */
export const EXITCLASS = 'delete from class_user where class_id=? and user_id=?'

/**
 * 获取班级信息
 */
export const GETCLASSINFO = `SELECT cs.class_id, count(cs.class_id) count, c.name name, c.user_id userId FROM class_user cs left join class c on cs.class_id=c.id GROUP BY class_id`

/**
 * 获取试卷信息
 */
export const GETPAPERTOTAL = `select c.name name, p.class from paper p left join class c on p.class=c.id where p.user_id=?`

/**
 * 获取题目总数
 */
export const GETSUBJECTSTOTAL = `select count(*) count from subjects`

/**
 * 获取班级总数
 */
export const GETTOTALCLASS = `select count(*) count from class where user_id=?`

/**
 * 获取班级的名字
 */
export const GETCLASSNAME = `select class.name name from class_user left join class on class_user.class_id=class.id where class_user.user_id=?`